package com.hex.ds.hdtp.core.inf.metadata.service.impl;

import cn.hutool.core.util.StrUtil;
import com.hex.ds.hdtp.core.inf.common.utils.JdbcUtil;
import com.hex.ds.hdtp.core.inf.metadata.po.SourceConfInfoPo;
import com.hex.ds.hdtp.core.inf.metadata.repository.ISourceConfInfoRepository;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Package com.hex.ds.hdtp.core.inf.metadata.service.impl
 * @ClassName AbstractORDBMSMethodService
 * @Description ORDBMS类型数据库（如Oracle、DM8）元数据管理抽象类
 * @Author gj.xu
 * @Date 2024/2/4 18:21
 * @Version v2.0
 **/
@Slf4j
@Service
public abstract class AbstractORDBMSMethodService extends AbstractSourceMethodService {

    public AbstractORDBMSMethodService(ISourceConfInfoRepository sourceConfInfoRepository) {
        super(sourceConfInfoRepository);
    }

    /**
     * @Method getSchemasSql
     * @Param
     * @Return java.lang.String
     * @Description 获取数据库Schemas的SQL
     * @Author gj.xu
     * @Date 2024/2/2 15:40
     * @Version V2.0
     */
    @Override
    public String getSchemasSql() {
        return "SELECT USERNAME FROM ALL_USERS;";
    }

    /**
     * @Method getTablesSql
     * @Param schemaName
     * @Return java.lang.String
     * @Description 获取数据库Tables的SQL
     * @Author gj.xu
     * @Date 2024/2/2 15:44
     * @Version V2.0
     */
    @Override
    public String getTablesSql(String schemaName) {
        return StrUtil.format("SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '{}'", schemaName);
    }

    @Override
    public List<String> procedures(String sourceCode, String schemaName) {
        List<SourceConfInfoPo> sourceConfInfoPoList = getSourceConfInfoRepository().queryListBySourceCode(sourceCode);
        Connection jdbcConnection = null;
        Statement jdbcStatement = null;
        ResultSet resultSet = null;
        List<String> resultList = new ArrayList();
        try {
            jdbcConnection = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            jdbcStatement = JdbcUtil.createJdbcStatement(jdbcConnection);
            resultSet = JdbcUtil.executeQuerySql(jdbcStatement, "SELECT object_name FROM all_procedures WHERE owner = '" + schemaName + "'");
            while (resultSet.next()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    resultList.add(resultSet.getString(i));
                }
            }
        } catch (SQLException e) {
            log.error("获取指定数据源的库集合", e.getStackTrace());
        } finally {
            JdbcUtil.closeConnection(resultSet);
            JdbcUtil.closeConnection(jdbcConnection, jdbcStatement);
        }
        return resultList.stream().sorted().collect(Collectors.toList());
    }

    @Override
    protected String queryProceduresContentSql(String schemaName, String proceduresName) {
        String query = "SELECT dbms_metadata.get_ddl('PROCEDURE','{}','{}') AS text FROM dual";
        return StrUtil.format(query, proceduresName, schemaName);
    }

    @Override
    protected String getProcedureContentFromResultSet(ResultSet resultSet) throws SQLException {
        return resultSet.getString("text");
    }


    @Override
    protected String queryTableStructureSql(String schemaName, String tableName) {
        String querySql = "SELECT \n" +
                "   t.COLUMN_ID, \n" +
                "   t.COLUMN_NAME, \n" +
                "   t.DATA_TYPE, \n" +
                "   t.TABLE_NAME, \n" +
                "   t.OWNER, \n" +
                "   (CASE WHEN t.DATA_PRECISION IS NULL \n" +
                "      THEN t.DATA_TYPE || '(' || t.CHAR_LENGTH || ')' \n" +
                "      ELSE t.DATA_TYPE || '(' || t.DATA_PRECISION || ',' || t.DATA_SCALE || ')' \n" +
                "      END) AS DATA_TYPE_LENGTH, \n" +
                "   (CASE WHEN t.DATA_PRECISION IS NULL \n" +
                "       THEN t.CHAR_LENGTH || '' \n" +
                "       ELSE '' || t.DATA_PRECISION || ',' || t.DATA_SCALE \n" +
                "       END) AS DATA_LENGTH, " +
                "   (CASE WHEN p.COLUMN_NAME = t.COLUMN_NAME THEN 'Y' ELSE 'N' END) AS IS_PARTITION_FIELD, \n" +
                "   (CASE WHEN k.COLUMN_NAME = t.COLUMN_NAME THEN 'Y' ELSE 'N' END) AS IS_PRIMARY_KEY, \n" +
                "   c.COMMENTS, \n" +
                "   n.NULLABLE \n" +
                "FROM  ALL_TAB_COLUMNS t \n" +
                "LEFT JOIN ALL_COL_COMMENTS c \n" +
                "   ON t.TABLE_NAME = c.TABLE_NAME \n" +
                "   AND t.OWNER = c.OWNER \n" +
                "   AND t.COLUMN_NAME = c.COLUMN_NAME \n" +
                "LEFT JOIN all_part_key_columns p \n" +
                "   ON t.OWNER = p.OWNER \n" +
                "   AND t.TABLE_NAME = p.NAME \n" +
                "LEFT JOIN ( \n" +
                "   SELECT ucc.COLUMN_NAME, uc.TABLE_NAME , uc.OWNER \n" +
                "   FROM user_cons_columns ucc \n" +
                "   LEFT JOIN user_constraints uc \n" +
                "   ON ucc.CONSTRAINT_NAME = uc.CONSTRAINT_NAME \n" +
                "   WHERE uc.CONSTRAINT_TYPE = 'P' \n" +
                "   ) k \n" +
                "   ON t.OWNER = k.OWNER \n" +
                "   AND t.TABLE_NAME = k.TABLE_NAME \n" +
                "LEFT JOIN all_tab_columns n \n" +
                "   ON t.OWNER = n.OWNER \n" +
                "   AND t.TABLE_NAME = n.TABLE_NAME \n" +
                "   AND t.COLUMN_NAME = n.COLUMN_NAME \n" +
                "WHERE \n" +
                "   t.OWNER = '{}' \n" +
                "   AND t.TABLE_NAME = '{}' \n" +
                "ORDER BY \n" +
                "   t.COLUMN_ID;";
        return StrUtil.format(querySql, schemaName.toUpperCase(), tableName.toUpperCase());
    }


    /**
     * @Method getTableAllFieldSqlLimitSql
     * @Param fields
     * @Param schema
     * @Param table
     * @Param whereSql
     * @Param extractScope
     * @Param offset
     * @Param sortField
     * @Return java.lang.String
     * @Description 获取分批比对全字段明细对比的SQL
     * @Author gj.xu
     * @Date 2024/2/1 20:53
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldSqlLimitSql(String fields, String schema, String table, String whereSql, Long extractScope, Long offset, String sortField) {
        String querySql = "SELECT {}\n" +
                "FROM (\n" +
                "  SELECT {}, ROW_NUMBER() OVER (ORDER BY {}) AS rnum\n" +
                "  FROM {}.{} {}\n" +
                ")\n" +
                "WHERE rnum BETWEEN {} AND {}";
        return StrUtil.format(querySql, fields, fields, sortField, schema, table, whereSql, offset + 1, offset + extractScope);
    }

    /**
     * @Method getTableShowCreateTableSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取查询表建表语句的SQL
     * @Author gj.xu
     * @Date 2024/1/31 17:18
     * @Version V2.0
     */
    @Override
    public String getTableShowCreateTableSql(String schema, String table) {
        String querySql = "SELECT DBMS_METADATA.GET_DDL('TABLE', '{}', '{}') FROM DUAL";
        return StrUtil.format(querySql, table, schema);
    }

    /**
     * @Method getTableAllFieldNumSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取字段总数SQL
     * @Author gj.xu
     * @Date 2024/2/1 11:12
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldNumSql(String schema, String table) {
        String querySql = "SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('{}') AND TABLE_NAME = UPPER('{}')";
        return StrUtil.format(querySql, schema, table);
    }

    /**
     * @Method getTableAllFieldNameSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取所有字段名称SQL
     * @Author gj.xu
     * @Date 2024/2/1 11:44
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldNameSql(String schema, String table) {
        String querySql = "SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('{}') AND TABLE_NAME = UPPER('{}')";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method getTableAllFieldLengthSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取所有字段长度SQL
     * @Author gj.xu
     * @Date 2024/2/1 14:20
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldLengthSql(String schema, String table) {
        String querySql = "SELECT COLUMN_NAME, DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('{}') AND TABLE_NAME = UPPER('{}')";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method getTableFieldAccuracySql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取字段精度SQL
     * @Author gj.xu
     * @Date 2024/2/1 15:20
     * @Version V2.0
     */
    @Override
    public String getTableFieldAccuracySql(String schema, String table) {
        String querySql = "SELECT COLUMN_NAME, COALESCE(DATA_SCALE, 0) as column_accuracy FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('{}') AND TABLE_NAME = UPPER('{}')";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method getTableAllFieldTypeSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取字段类型SQL
     * @Author gj.xu
     * @Date 2024/2/1 15:45
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldTypeSql(String schema, String table) {
        String querySql = "SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE OWNER = UPPER('{}') AND TABLE_NAME = UPPER('{}')";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method queryTableExistSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取表是否存在SQL
     * @Author gj.xu
     * @Date 2024/2/1 17:02
     * @Version V2.0
     */
    public String queryTableExistSql(String schema, String table) {
        String querySql = "SELECT COUNT(*) FROM ALL_TABLES WHERE OWNER = UPPER('{}') AND TABLE_NAME = UPPER( '{}' )";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method getTablePartitionFieldSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取分区字段的SQL
     * @Author gj.xu
     * @Date 2024/2/1 20:20
     * @Version V2.0
     */
    @Override
    public String getTablePartitionFieldSql(String schema, String table) {
        String querySql = "SELECT COLUMN_NAME FROM ALL_PART_KEY_COLUMNS t WHERE t.owner = UPPER('{}') AND t.name = UPPER('{}')";
        return StrUtil.format(querySql, schema, table);
    }


    /**
     * @Method getCreateTableBodySql
     * @Param columnName
     * @Param columnType
     * @Param columnExt
     * @Return java.lang.String
     * @Description 获取创建表时Body部分SQL
     * @Author gj.xu
     * @Date 2024/2/1 17:36
     * @Version V2.0
     */
    public String getCreateTableBodySql(String columnName, String columnType, String columnExt) {
        String querySql = "{} {} {} ,\n";
        return StrUtil.format(querySql, columnName, columnType, columnExt);
    }


    /**
     * @Method getCreateTablePartitionSql
     * @Param columnName
     * @Return java.lang.String
     * @Description 获取创建表时分区部分SQL
     * @Author gj.xu
     * @Date 2024/2/1 18:50
     * @Version V2.0
     */
    public String getCreateTablePartitionSql(String columnName) {
        String querySql = "PARTITION BY RANGE ({}) INTERVAL (numtodsinterval(10,'day')) \n" +
                "(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))\n" +
                ");";
        return StrUtil.format(querySql, columnName);
    }

    /**
     * @Method getStrDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取字符数据类型
     * @Author gj.xu
     * @Date 2024/2/21 14:20
     * @Version V2.0
     */
    @Override
    public List<String> getStrDataType() {
        return Arrays.asList("CHAR", "VARCHAR2", "CLOB", "NCHAR", "NVARCHAR2", "NCLOB", "RAW", "BLOB", "BFILE");
    }

    /**
     * @Method getNumDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取数值数据类型
     * @Author gj.xu
     * @Date 2024/2/21 15:10
     * @Version V2.0
     */
    @Override
    public List<String> getNumDataType() {
        return Arrays.asList("NUMBER", "BINARY_FLOAT", "BINARY_DOUBLE", "FLOAT", "DECIMAL", "LONG", "INTEGER", "SMALLINT", "BIGINT");
    }

    /**
     * @Method getAccuracyDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取精度数据类型
     * @Author gj.xu
     * @Date 2024/2/21 15:32
     * @Version V2.0
     */
    @Override
    public List<String> getAccuracyDataType() {
        return Arrays.asList("NUMBER", "FLOAT", "BINARY_FLOAT", "BINARY_DOUBLE");
    }
}
